with tmp_network as (
    select case
               when network_type = 3 then 1 --加盟
               when network_type = 4 then 2 --中心
               when network_type = 5 then 3 --集散
               when network_type = 6 then 4 --网点
               when network_type = 2 then 5 --代理区
               when network_type = 1 then 6 --总部
               else 0
        end            as network_type
         , code
         , name        as network_name     --网点名称
         , agent_code                      --代理区编码
         , agent_name                      --代理区名称
         , fran_code   as franchisee_code  --加盟商编码
         , fran_name   as franchisee_name  --加盟商名称
         , center_code as transcenter_code --转运中心编码
         , center_name as transcenter_name --转运中心名称
    from jms_dim.dim_network_whole_massage
),

tab_barscan_tmp as (
    select tmp_barscan.recordid              as record_id
         , tmp_barscan.billcode              as waybill_no
         , tmp_barscan.listcode              as list_code
         , tmp_barscan.subbillcode           as subbill_code
         , tmp_barscan.transfercode          as shipment_no
         , tmp_barscan.packagecode           as package_code
         , tmp_barscan.scantype              as scan_type
         , tmp_barscan.nextstation           as next_station
         , tmp_barscan.destination           as destination
         , tmp_barscan.scanuser              as scan_user
         , tmp_barscan.inputsite             as scan_site
         , tmp_barscan.scantime              as scan_time
         , tmp_barscan.inputtime             as input_time
         , tmp_barscan.operatedate           as operate_date
         , tmp_barscan.send_deliver_user     as send_deliver_user
         , tmp_barscan.sendcustomer          as send_customer
         , tmp_barscan.sendsite              as send_site
         , tmp_barscan.destsite              as dest_site
         , tmp_barscan.transfercenter        as transfer_center
         , tmp_barscan.pcs                   as pcs
         , tmp_barscan.weight                as weight
         , tmp_barscan.goodstype             as goods_type
         , tmp_barscan.expresstype           as transport_type
         , tmp_barscan.shifts                as shifts
         , tmp_barscan.transfer_deliver_fee  as transfer_deliver_fee
         , tmp_barscan.networkfee            as network_manage_fee
         , tmp_barscan.otherfee              as other_fee
         , tmp_barscan.checkflag             as check_flag
         , tmp_barscan.accountflag           as account_flag
         , tmp_barscan.receiptflag           as receipt_flag
         , tmp_barscan.returnflag            as return_flag
         , tmp_barscan.elescaleflag          as ele_scale_flag
         , tmp_barscan.unrecordflag          as unrecord_flag
         , tmp_barscan.writebackflag         as write_back_flag
         , tmp_barscan.lockflag              as auto_produce_type
         , tmp_barscan.remark                as remark
         , tmp_barscan.modifyuser            as station_num
         , tmp_barscan.modifytime            as modify_time
         , tmp_barscan.mobile                as mobile
         , tmp_barscan.pistolid              as pistol_id
         , tmp_barscan.remark1               as remark1
         , tmp_barscan.remark2               as flight_num
         , tmp_barscan.scansitecode          as scan_site_code
         , tmp_barscan.uploadtime            as upload_time
         , tmp_barscan.remark3               as remark3
         , tmp_barscan.scanusercode          as scan_user_code
         , tmp_barscan.send_deliver_usercode as send_deliver_user_code
         , tmp_barscan.nextstationcode       as next_station_code
         , tmp_barscan.source                as source
         , tmp_barscan.remark5               as remark5
         , tmp_barscan.remark6               as remark6
         , tmp_barscan.remark4               as list_code_2
         , tmp_barscan.scansiteid            as scan_site_id
         , tmp_barscan.nextstationid         as next_station_id
         , tmp_barscan.scanuserid            as scan_user_id
         , tmp_barscan.send_deliver_userid   as send_deliver_user_id
         , tmp_network.network_type          as network_type
         , tmp_network.agent_code            as site_agent_code
         , tmp_network.agent_name            as site_agent_name
         , tmp_network.franchisee_code       as site_franchisee_code
         , tmp_network.franchisee_name       as site_franchisee_name
         , tmp_network.transcenter_code      as site_transcenter_code
         , tmp_network.transcenter_name      as site_transcenter_name
         , tmp_network_next.network_type     as next_network_type
         , case
               WHEN (LENGTH(BILLCODE) = 13) THEN 'GB'
               WHEN (LENGTH(BILLCODE) = 15 AND BILLCODE LIKE "55%") THEN 'BS'
               WHEN (LENGTH(BILLCODE) = 12 AND (BILLCODE LIKE "40%" OR BILLCODE LIKE "42%")) THEN 'BSB'
               WHEN (LENGTH(BILLCODE) = 15 AND BILLCODE LIKE "JT%") THEN 'JT'
               WHEN (LENGTH(BILLCODE) = 13 AND BILLCODE LIKE "B%") THEN 'JTB'
               else "others"
        end                                  as waybill_source
         ,sys_network.IS_DISTRIBUTION_CENTER
         , tmp_barscan.dt as dt
    from jms_ods.tab_barscan_arrival_hi tmp_barscan
    left join tmp_network tmp_network on tmp_barscan.scansitecode = tmp_network.code
    left join tmp_network tmp_network_next on tmp_barscan.nextstationcode = tmp_network_next.code
    left join jms_dim.dim_lmdm_sys_network sys_network on tmp_barscan.scansitecode = sys_network.code
    where tmp_barscan.dt = '{{ execution_date | cst_hour }}'
)

insert overwrite table jms_dwd.dwd_tab_barscan_sitearrival_base_hi partition(dt)
select
    record_id
    ,waybill_no
    ,list_code
    ,subbill_code
    ,shipment_no
    ,package_code
    ,scan_type
    ,next_station
    ,destination
    ,scan_user
    ,scan_site
    ,scan_time
    ,input_time
    ,operate_date
    ,send_deliver_user
    ,send_customer
    ,send_site
    ,dest_site
    ,transfer_center
    ,pcs
    ,weight
    ,goods_type
    ,transport_type
    ,shifts
    ,transfer_deliver_fee
    ,network_manage_fee
    ,other_fee
    ,check_flag
    ,account_flag
    ,receipt_flag
    ,return_flag
    ,ele_scale_flag
    ,unrecord_flag
    ,write_back_flag
    ,auto_produce_type
    ,remark
    ,station_num
    ,modify_time
    ,mobile
    ,pistol_id
    ,remark1
    ,flight_num
    ,scan_site_code
    ,upload_time
    ,remark3
    ,scan_user_code
    ,send_deliver_user_code
    ,next_station_code
    ,source
    ,remark5
    ,remark6
    ,list_code_2
    ,scan_site_id
    ,next_station_id
    ,scan_user_id
    ,send_deliver_user_id
    ,network_type
    ,site_agent_code
    ,site_agent_name
    ,site_franchisee_code
    ,site_franchisee_name
    ,site_transcenter_code
    ,site_transcenter_name
    ,next_network_type
    ,waybill_source
    ,dt
from tab_barscan_tmp
where IS_DISTRIBUTION_CENTER = 2;